package com.aotain.oframework.util;

import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

import com.aotain.oframework.dto.DTOUtil;

/**
 * @author Jacob.cw.Zhang@gmail.com
 */
public class ExcelUtil {
	
	public static final short FGColor_Green = 42;
	public static final short FGColor_Yellow = 13;
	public static final short FGColor_LightYellow = 43;
	public static final short FGColor_Red = 10;
	public static final short BGColor = 64;
	
	public static final short BorderBottom = 1;
	public static final short BorderTop = 1;
	public static final short BorderLeft = 1;
	public static final short BorderRight = 1;
	
	public static final short LeftBorderColor = 64;
	public static final short RightBorderColor = 64;
	public static final short TopBorderColor = 64;
	public static final short BottomBorderColor = 64;
	
	public static final short Alignment = 1;
	public static final short Vertical = 2;
	
	@SuppressWarnings("rawtypes")
	public static HSSFWorkbook  writeWorkBook(String[] Header,Map<String,String> maping,List datas,String caption){
		 HSSFWorkbook workbook = new HSSFWorkbook();   
		 HSSFCellStyle headerStyle =doHeadStyle(workbook);
		 HSSFSheet sheet = workbook.createSheet(caption);
		 if(Header == null || Header.length == 0){
			 return null;
		 }
		 
		 /**
		  * write excel header
		  */
		 HSSFCell cell = null;
		 HSSFRow row = sheet.createRow(0);
		 for(int i = 0;i<Header.length;i++){
			 cell = row.createCell(i);
			 cell.setCellStyle(headerStyle);
			 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			 cell.setCellValue(Header[i]);
		 }
		 
		 /**
		  * write datas
		  */
		 String columnName = "";
		 String fieldName = "";
		 Object jb = null;
		 Object value = null;
		 HSSFRow brow = null;
		 HSSFCell bcell = null;
		 HSSFCellStyle cellStyle = excelBodyStyle(workbook);
		 
		 int bit = 1;
		 if(datas != null && datas.size() > 0){
			 for(int j=0;j<datas.size();j++){
				 jb = datas.get(j);
				 brow = sheet.createRow(bit++); 
				 for(int i = 0;i < Header.length;i++){
					 columnName =  Header[i];
					 fieldName = maping.get(columnName).toString();
					 value = DTOUtil.getValueByFieldName(fieldName, jb);
					 System.out.println(columnName.toString()+":........tt.......");
					 bcell = brow.createCell(i);
					 bcell.setCellStyle(cellStyle);
					 bcell.setCellValue(value == null?"":value.toString());
				 }
			 }
		 }
		 for(int i = 0;i<Header.length;i++){
			 columnName =  Header[i];
			 int cellLength = columnName.getBytes().length;
			 sheet.setColumnWidth(i,cellLength*2*256+30);
		 }
    	return workbook;
    }
    
    /**
     * Excel Header Style
     * @param workbook
     * @return
     */
    public static HSSFCellStyle doHeadStyle(HSSFWorkbook workbook){
    	HSSFCellStyle headerStyle = workbook.createCellStyle();
    	headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    	headerStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
    	headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
    	headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    	headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    	headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    	headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
    	headerStyle.setFont(getTitleFont(workbook));
		return headerStyle;
    }
    
    /**
	 * set Head font
	 * @param workbook
	 * @return
	 */
	public static HSSFFont getTitleFont(HSSFWorkbook workbook){					
		HSSFFont font = workbook.createFont();
		font.setFontName("黑体");
		font.setFontHeightInPoints((short)12);	
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		return font;
	}
	
    /**
     * Excel Body Style
     * @param workbook
     * @return
     */
	public static HSSFCellStyle excelBodyStyle(HSSFWorkbook workbook){
    	HSSFCellStyle cellStyle = workbook.createCellStyle();
    	cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
    	cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    	cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    	cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		return cellStyle;
    }
}
